-- extended event session to monitor query performance
IF EXISTS(SELECT 1 FROM sys.server_event_sessions WHERE name = N'QueryPerformance')
	DROP EVENT SESSION QueryPerformance ON SERVER;
GO

-- create event session
CREATE EVENT SESSION QueryPerformance ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(
    ACTION (
		sqlserver.query_hash
	)
    WHERE sqlserver.database_id = <database_id> AND sqlserver.query_hash <> 0),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION (
		sqlserver.query_hash
	)
    WHERE sqlserver.database_id = <database_id> AND sqlserver.query_hash <> 0)
ADD TARGET package0.event_file
(
	SET FILENAME = N'C:\Temp\QueryPerformance.xel',
		MAX_FILE_SIZE = 200, MAX_ROLLOVER_FILES = 5
);

-- enable event session
ALTER EVENT SESSION QueryPerformance ON SERVER STATE = START;
GO

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION QueryPerformance ON SERVER STATE = STOP;
GO

-- extract query performance info into a temp table #XEResults
SELECT
  event_data.value ('(event/action[@name="query_hash"]/value)[1]', 'binary(8)') AS query_hash,
  event_data.value ('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration,
  event_data.value ('(event/data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time,
  event_data.value ('(event/data[@name="physical_reads"]/value)[1]', 'bigint') AS physical_reads,
  event_data.value ('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
  event_data.value ('(event/data[@name="writes"]/value)[1]', 'bigint') AS writes,
  event_data.value ('(event/data[@name="row_count"]/value)[1]', 'bigint') AS row_count,
  event_data.value ('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS 'statement'
INTO #XEResults
FROM (
	SELECT
		CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file
	(N'C:\Temp\QueryPerformance*xel', NULL, NULL, NULL)
) AS queries;

CREATE CLUSTERED INDEX ci_query_hash ON #XEResults(query_hash);

-- examine query info
SELECT * FROM #XEResults;

-- group by query hash and compute running total percent
SELECT
	query_hash,
	COUNT(*) AS num_queries,
	SUM(logical_reads) AS total_logical_reads,
	CAST(100.0 * SUM(logical_reads)
             / SUM(SUM(logical_reads)) OVER() AS DECIMAL(5, 2)) AS pct,
	CAST(100.0 * SUM(SUM(logical_reads)) OVER(ORDER BY SUM(logical_reads) DESC
                                            ROWS UNBOUNDED PRECEDING)
             / SUM(SUM(logical_reads)) OVER()
       AS DECIMAL(5, 2)) AS running_pct
FROM #XEResults
GROUP BY query_hash
ORDER BY total_logical_reads DESC;

-- group by query_hash for the top logical I/O queries, filter and include a sample query
WITH QueryHashTotals AS
(
  SELECT
	query_hash,
    COUNT(*) AS num_queries,
    SUM(logical_reads) AS total_logical_reads
  FROM #XEResults
  GROUP BY query_hash
),
RunningTotals AS
(
  SELECT query_hash, num_queries, total_logical_reads,
    CAST(100. * total_logical_reads
              / SUM(total_logical_reads) OVER()
         AS DECIMAL(5, 2)) AS pct,
    CAST(100. * SUM(total_logical_reads) OVER(ORDER BY total_logical_reads DESC
                                             ROWS UNBOUNDED PRECEDING)
              / SUM(total_logical_reads) OVER()
         AS DECIMAL(5, 2)) AS running_pct
  FROM QueryHashTotals
)
SELECT RT.*, (SELECT TOP (1) statement
              FROM #XEResults AS Q
              WHERE Q.query_hash = RT.query_hash) AS sample_statement
FROM RunningTotals AS RT
WHERE running_pct - pct < 80.00
ORDER BY total_logical_reads DESC;

-- group by query_hash for the top cpu queries, filter and include a sample query
WITH QueryHashTotals AS
(
  SELECT
	query_hash,
    COUNT(*) AS num_queries,
    SUM(cpu_time) AS total_cpu_time
  FROM #XEResults
  GROUP BY query_hash
),
RunningTotals AS
(
  SELECT query_hash, num_queries, total_cpu_time,
    CAST(100. * total_cpu_time
              / SUM(total_cpu_time) OVER()
         AS DECIMAL(5, 2)) AS pct,
    CAST(100. * SUM(total_cpu_time) OVER(ORDER BY total_cpu_time DESC
                                             ROWS UNBOUNDED PRECEDING)
              / SUM(total_cpu_time) OVER()
         AS DECIMAL(5, 2)) AS running_pct
  FROM QueryHashTotals
)
SELECT RT.*, (SELECT TOP (1) statement
              FROM #XEResults AS Q
              WHERE Q.query_hash = RT.query_hash) AS sample_statement
FROM RunningTotals AS RT
WHERE running_pct - pct < 80.00
ORDER BY total_cpu_time DESC;

-- drop the temp table
DROP TABLE #XEResults;

-- cleanup
DROP EVENT SESSION QueryPerformance ON SERVER;
GO